package com.dao.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.dao.IBisMdlDefDao;
import com.dao.IRetBoxDao;
import com.dao.IRetPrdInDao;
import com.dao.IRetPrdIndtDao;
import com.dao.IRetPrdInfoDao;
import com.dao.IRetPrdJgeDao;
import com.dao.IRetWODao;
import com.model.Bis_mdl_def;
import com.model.Ret_box;
import com.model.Ret_prd_in;
import com.model.Ret_prd_indt;
import com.model.Ret_prd_info;
import com.model.Ret_wo;
import com.rtnBean.BoxInfoBeen;
import com.rtnBean.SimpleRtnBeen;
import com.tx.Xpdefect.DefectInfoBeen2;
import com.util.DoubleUtil;
import com.util.Util;
import static com.def.GenericDef.*;

@Repository
public class RetBoxHibernateDao extends GenericHibernateDao<Ret_box, String>
		implements IRetBoxDao {
	private static Logger logger = Logger.getLogger(RetBoxHibernateDao.class);
	@Autowired
	private IRetPrdInfoDao retPrdInfoDao;
	@Autowired
	private IRetPrdInDao retPrdInDao;
	@Autowired
	private IRetPrdIndtDao retPrdIndtDao;
	@Autowired
	private IRetWODao retWODao;
	@Autowired
	private IBisMdlDefDao bisMdlDefDao;
	@Autowired
	private IRetPrdJgeDao retPrdJgeDao;

	public Ret_box setBoxEmpt(Ret_box box) {
		box.setBox_stat(PROC_EMPTY);
		box.setReal_emp(EMPTY_CARRIER);
		box.setGroup_id(_SPACE0);
		box.setBox_use_cnt(box.getBox_use_cnt() + 1);
		box.setPrd_grade_fk(_SPACE0);
		box.setPrd_qty(0);
		box.setLot_id_fk(_SPACE);
		box.setMdl_typ_fk(_SPACE0);
		box.setMdl_cate_fk(_SPACE0);
		box.setMdl_id_fk(_SPACE0);
		box.setMdl_ec_code_fk(_SPACE0);
		box.setMtrl_prod_id_fk(_SPACE0);
		box.setNx_path_id_fk(_SPACE0);
		box.setNx_path_ver_fk(_SPACE0);
		box.setNx_ope_id_fk(_SPACE0);
		box.setNx_ope_no_fk(_SPACE0);
		box.setNx_ope_ver_fk(_SPACE0);
		box.setNx_proc_id_fk(_SPACE0);
		box.setNx_tool_id_fk(_SPACE0);
		box.setNx_toolg_id_fk(_SPACE0);
		box.setTool_id_fk(_SPACE0);
		box.setTool_port_id_fk(_SPACE0);
		box.setPv_prty(box.getPrty());
		box.setPrty(_SPACE0);
		box.setWo_id_fk(_SPACE0);
		box.setDest_shop(_SPACE0);
		box.setSo_id_fk(_SPACE0);
		box.setPallet_id_fk(_SPACE0);
		box.setBatch_no_fk(_SPACE0);
		box.setRsv_no_fk(_SPACE0);
		box.setDn_no_fk(_SPACE0);
		box.setCus_no(_SPACE0);
		box.setPrd_admin_flg("NNNNNNNNNNNNNNNNNNNN");
		return box;
	}

	public SimpleRtnBeen configBoxInfo(Ret_box box) {
		SimpleRtnBeen simpleRtnBeen = new SimpleRtnBeen(_NORMAL);
		Boolean isMixLot = false;
		// Get prd list
		List<Ret_prd_info> gRet_prd_infoList = new ArrayList<Ret_prd_info>();
		gRet_prd_infoList = retPrdInfoDao.getShtListInfo(box.getBox_id());
		if (null == gRet_prd_infoList) {
			// EMPT BOX
			simpleRtnBeen.setObj(this.setBoxEmpt(box));
		} else {
			// Not empt
			if (Util.isSpaceCheck(gRet_prd_infoList.get(0).getLot_id())) {
				simpleRtnBeen.setMsgBuf("箱子 [" + box.getBox_id() + "]中["
						+ gRet_prd_infoList.get(0).getPrd_seq_id()
						+ "]批次号为空,请检查]!");
				simpleRtnBeen.setlRc(SHT_LOT_NULL);
				return simpleRtnBeen;
			}
			// Check count
			if (gRet_prd_infoList.size() > box.getStd_qty()) {
				simpleRtnBeen.setMsgBuf("箱子 [" + box.getBox_id() + "]最大数量为["
						+ box.getStd_qty() + "]不能装入["
						+ gRet_prd_infoList.size() + "]片玻璃!");
				simpleRtnBeen.setlRc(BOX_CNT_NOT_ENOUGT);
				return simpleRtnBeen;
			}

			Ret_wo ret_wo = retWODao.get(gRet_prd_infoList.get(0).getWo_id_fk());
			if(ret_wo == null){
				simpleRtnBeen.setMsgBuf("找不到箱子 [" + box.getBox_id() + "]的内部订单["
						+ gRet_prd_infoList.get(0).getWo_id_fk() + "]!");
				simpleRtnBeen.setlRc(E_RET_WO + E_READ_NOT_FOUND);
				return simpleRtnBeen;
			}
			
			// Check mix
			for (int i = 1; i < gRet_prd_infoList.size(); i++) {
				// Check status
				if (!gRet_prd_infoList.get(0).getPrd_stat()
						.equals(gRet_prd_infoList.get(i).getPrd_stat())) {
					simpleRtnBeen.setMsgBuf("状态不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getPrd_stat() + "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getPrd_stat() + "]!");
					simpleRtnBeen.setlRc(SHT_STAT_MIX);
					return simpleRtnBeen;
				}

				// Check NX_PATH_ID
				if (!gRet_prd_infoList.get(0).getNx_path_id_fk()
						.equals(gRet_prd_infoList.get(i).getNx_path_id_fk())) {
					simpleRtnBeen.setMsgBuf("下一工艺路线不能混！["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getNx_path_id_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getNx_path_id_fk()
							+ "]!");
					simpleRtnBeen.setlRc(SHT_PATH_ID_MIX);
					return simpleRtnBeen;
				}

				// Check NX_PATH_VER_FK
				if (!gRet_prd_infoList.get(0).getNx_path_ver_fk()
						.equals(gRet_prd_infoList.get(i).getNx_path_ver_fk())) {
					simpleRtnBeen.setMsgBuf("下一工艺路线版本不能混！["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getNx_path_ver_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getNx_path_ver_fk()
							+ "]!");
					simpleRtnBeen.setlRc(SHT_PATH_VER_MIX);
					return simpleRtnBeen;
				}

				// Check NX_OPE_NO_FK
				if (!gRet_prd_infoList.get(0).getNx_ope_no_fk()
						.equals(gRet_prd_infoList.get(i).getNx_ope_no_fk())) {
					simpleRtnBeen
							.setMsgBuf("下一站点编号不能混！ ["
									+ gRet_prd_infoList.get(0).getPrd_seq_id()
									+ "]["
									+ gRet_prd_infoList.get(0)
											.getNx_ope_no_fk()
									+ "] vs ["
									+ gRet_prd_infoList.get(i).getPrd_seq_id()
									+ "]["
									+ gRet_prd_infoList.get(i)
											.getNx_ope_no_fk() + "]!");
					simpleRtnBeen.setlRc(SHT_OPE_NO_MIX);
					return simpleRtnBeen;
				}

				// Check NX_OPE_ID_FK
				if (!gRet_prd_infoList.get(0).getNx_ope_id_fk()
						.equals(gRet_prd_infoList.get(i).getNx_ope_id_fk())) {
					simpleRtnBeen
							.setMsgBuf("下一站点ID不能混！ ["
									+ gRet_prd_infoList.get(0).getPrd_seq_id()
									+ "]["
									+ gRet_prd_infoList.get(0)
											.getNx_ope_id_fk()
									+ "] vs ["
									+ gRet_prd_infoList.get(i).getPrd_seq_id()
									+ "]["
									+ gRet_prd_infoList.get(i)
											.getNx_ope_id_fk() + "]!");
					simpleRtnBeen.setlRc(SHT_OPE_ID_MIX);
					return simpleRtnBeen;
				}

				// Check NX_OPE_VER_FK
				if (!gRet_prd_infoList.get(0).getNx_ope_ver_fk()
						.equals(gRet_prd_infoList.get(i).getNx_ope_ver_fk())) {
					simpleRtnBeen.setMsgBuf("下一站点版本不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getNx_ope_ver_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getNx_ope_ver_fk()
							+ "]!");
					simpleRtnBeen.setlRc(SHT_OPE_VER_MIX);
					return simpleRtnBeen;
				}

				// Check NX_PROC_ID_FK
				if (!gRet_prd_infoList.get(0).getNx_proc_id_fk()
						.equals(gRet_prd_infoList.get(i).getNx_proc_id_fk())) {
					simpleRtnBeen.setMsgBuf("下一制程不能混！["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getNx_proc_id_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getNx_proc_id_fk()
							+ "]!");
					simpleRtnBeen.setlRc(SHT_NX_PROC_ID_MIX);
					return simpleRtnBeen;
				}

				// Check NX_TOOLG_ID_FK
				if (!gRet_prd_infoList.get(0).getNx_toolg_id_fk()
						.equals(gRet_prd_infoList.get(i).getNx_toolg_id_fk())) {
					simpleRtnBeen.setMsgBuf("下一设备组不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getNx_toolg_id_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getNx_toolg_id_fk()
							+ "]!");
					simpleRtnBeen.setlRc(SHT_NX_TOOLG_MIX);
					return simpleRtnBeen;
				}

				// Check WO_ID_FK
				if (!gRet_prd_infoList.get(0).getWo_id_fk()
						.equals(gRet_prd_infoList.get(i).getWo_id_fk())) {
					simpleRtnBeen.setMsgBuf("内部订单不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getWo_id_fk() + "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getWo_id_fk() + "]!");
					simpleRtnBeen.setlRc(SHT_WO_ID_MIX);
					return simpleRtnBeen;
				}

				//Check GROUP_ID
				if(_YES.equals(ret_wo.getSwh_cnt_flg())){//客户订单检查group id
					if (!gRet_prd_infoList.get(0).getGroup_id()
							.equals(gRet_prd_infoList.get(i).getGroup_id())) {
						simpleRtnBeen.setMsgBuf("组代码不能混！ ["
								+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
								+ gRet_prd_infoList.get(0).getGroup_id() + "] vs ["
								+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
								+ gRet_prd_infoList.get(i).getGroup_id() + "]!");
						simpleRtnBeen.setlRc(GROUP_ID_MIX);
						return simpleRtnBeen;
					}
				}
				
				// Check MDL_ID_FK
				if (!gRet_prd_infoList.get(0).getMdl_id_fk()
						.equals(gRet_prd_infoList.get(i).getMdl_id_fk())) {
					simpleRtnBeen.setMsgBuf("产品ID不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getMdl_id_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getMdl_id_fk() + "]!");
					simpleRtnBeen.setlRc(SHT_MDL_ID_MIX);
					return simpleRtnBeen;
				}

				// Check MDL_TYPE_FK
				if (!gRet_prd_infoList.get(0).getMdl_typ_fk()
						.equals(gRet_prd_infoList.get(i).getMdl_typ_fk())) {
					simpleRtnBeen.setMsgBuf("产品类型不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getMdl_typ_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getMdl_typ_fk() + "]!");
					simpleRtnBeen.setlRc(SHT_MDL_TYPE_MIX);
					return simpleRtnBeen;
				}

				// Check MDL_CATE_FK
				if (!gRet_prd_infoList.get(0).getMdl_cate_fk()
						.equals(gRet_prd_infoList.get(i).getMdl_cate_fk())) {
					simpleRtnBeen.setMsgBuf("产品种类不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getMdl_cate_fk()
							+ "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getMdl_cate_fk() + "]!");
					simpleRtnBeen.setlRc(SHT_MDL_CATE_MIX);
					return simpleRtnBeen;
				}

				// Check BNK_FLG
				if (!gRet_prd_infoList.get(0).getBnk_flg()
						.equals(gRet_prd_infoList.get(i).getBnk_flg())) {
					simpleRtnBeen.setMsgBuf("入库标示不能混！ ["
							+ gRet_prd_infoList.get(0).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(0).getBnk_flg() + "] vs ["
							+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]["
							+ gRet_prd_infoList.get(i).getBnk_flg() + "]!");
					simpleRtnBeen.setlRc(SHT_BNK_FLG_MIX);
					return simpleRtnBeen;
				}

				// Check lot_id
				if (Util.isSpaceCheck(gRet_prd_infoList.get(i).getLot_id())) {
					simpleRtnBeen.setMsgBuf("箱子 [" + box.getBox_id() + "]中["
							+ gRet_prd_infoList.get(i).getPrd_seq_id()
							+ "]批次号为空,请检查]!");
					simpleRtnBeen.setlRc(SHT_LOT_NULL);
					return simpleRtnBeen;
				}
				if (!isMixLot) {
					if (!gRet_prd_infoList.get(0).getLot_id()
							.equals(gRet_prd_infoList.get(i).getLot_id())) {
						isMixLot = true;
					}
				}
				// TODO Check x-y & layout id
			} // End of mix check

			// Check slot
			simpleRtnBeen = slotCheck(gRet_prd_infoList);
			if (_NORMAL != simpleRtnBeen.getlRc()) {
				return simpleRtnBeen;
			}

			// set box info
			if (PROC_IN_PROCESS.equals(gRet_prd_infoList.get(0).getPrd_stat())) {
				box.setBox_stat(PROC_EMPTY);
			} else {
				box.setBox_stat(gRet_prd_infoList.get(0).getPrd_stat());
			}
			box.setReal_emp(EXIST_CARRIER);
			if (isMixLot) {
				box.setLot_id_fk(LOT_MIX);
			} else {
				box.setLot_id_fk(gRet_prd_infoList.get(0).getLot_id());
			}

			// Set prd grade
			if (retPrdInfoDao.getOkPrdCnt(box.getBox_id()) > 0) {
				box.setPrd_grade_fk(_OK);
			} else {
				box.setPrd_grade_fk(_NG);
			}

			// Path info
			box.setNx_path_id_fk(gRet_prd_infoList.get(0).getNx_path_id_fk());
			box.setNx_path_ver_fk(gRet_prd_infoList.get(0).getNx_path_ver_fk());
			box.setNx_ope_no_fk(gRet_prd_infoList.get(0).getNx_ope_no_fk());
			box.setNx_ope_id_fk(gRet_prd_infoList.get(0).getNx_ope_id_fk());
			box.setNx_ope_ver_fk(gRet_prd_infoList.get(0).getNx_ope_ver_fk());

			// Prod & tool
			box.setNx_proc_id_fk(gRet_prd_infoList.get(0).getNx_proc_id_fk());
			box.setMdl_id_fk(gRet_prd_infoList.get(0).getMdl_id_fk());
			box.setMdl_typ_fk(gRet_prd_infoList.get(0).getMdl_typ_fk());
			box.setMdl_cate_fk(gRet_prd_infoList.get(0).getMdl_cate_fk());
			box.setMtrl_prod_id_fk(gRet_prd_infoList.get(0)
					.getMtrl_prod_id_fk());

			box.setNx_toolg_id_fk(gRet_prd_infoList.get(0).getNx_toolg_id_fk());
			box.setNx_tool_id_fk(_SPACE0);

			box.setWo_id_fk(gRet_prd_infoList.get(0).getWo_id_fk());
			box.setBatch_no_fk(gRet_prd_infoList.get(0).getBatch_no());
			box.setBnk_flg(gRet_prd_infoList.get(0).getBnk_flg());
			box.setValid_flg(_YES);
			box.setPrty(gRet_prd_infoList.get(0).getPrty());
			box.setPv_prty(gRet_prd_infoList.get(0).getPv_prty());
			box.setDest_shop(gRet_prd_infoList.get(0).getDest_shop());
			box.setSo_id_fk(gRet_prd_infoList.get(0).getSo_id_fk());
			box.setPrd_qty(gRet_prd_infoList.size());
			if (null != gRet_prd_infoList.get(0).getPrd_admin_flg()){
				box.setPrd_admin_flg(gRet_prd_infoList.get(0).getPrd_admin_flg().substring(0, 1)+"NNNNNNNNNNNNNNNNNNN");
			}else{
				box.setPrd_admin_flg("NNNNNNNNNNNNNNNNNNNN");
			}
			box.setGroup_id(gRet_prd_infoList.get(0).getGroup_id());//更新箱子的group id
			simpleRtnBeen.setObj(box);
			
			this.update(box);
		}

		return simpleRtnBeen;
	}

	public SimpleRtnBeen slotCheck(List<Ret_prd_info> gRet_prd_infoList) {
		SimpleRtnBeen simpleRtnBeen = new SimpleRtnBeen(_NORMAL);
		String slotNO1 = "";
		for (int i = 0; i < gRet_prd_infoList.size(); i++) {
			slotNO1 = gRet_prd_infoList.get(i).getSlot_no();
			if (!(Integer.valueOf(slotNO1) > 0)) {
				simpleRtnBeen.setMsgBuf("产品 ["
						+ gRet_prd_infoList.get(i).getPrd_seq_id() + "]的子格位号 ["
						+ slotNO1 + "]不正确");
				simpleRtnBeen.setlRc(SLOT_NO_INVALIED);
				return simpleRtnBeen;
			}
		}
		return simpleRtnBeen;
	}

	public SimpleRtnBeen chkMtrlBoxAndSlot(Ret_box box, boolean chkSlot) {
		SimpleRtnBeen rtnBeen = new SimpleRtnBeen(_NORMAL);
		String msgBuf = "";
		List<Ret_prd_info> prdInfoList = new ArrayList<Ret_prd_info>();

		prdInfoList = retPrdInfoDao.getShtListInfo(box.getBox_id());
		if (null == prdInfoList) {
			msgBuf = "箱子 [" + box.getBox_id() + "] 里面没有玻璃 !";
			return new SimpleRtnBeen(E_RET_PRD_INFO + E_READ_NOT_FOUND, msgBuf);
		}

		Ret_prd_in ret_prd_in = new Ret_prd_in();
		List<Ret_prd_indt> ret_prd_indtList = new ArrayList<Ret_prd_indt>();

		// Get mtrl box info
		rtnBeen = retPrdInDao.getRetPrdInByWoAndBox(box.getWo_id_fk(),
				box.getBox_id());
		if (_NORMAL != rtnBeen.getlRc()) {
			return rtnBeen;
		} else {
			ret_prd_in = (Ret_prd_in) rtnBeen.getObj();
		}

		// Get mtrl panel info
		ret_prd_indtList = retPrdIndtDao.getPrdIndtByBox(box.getBox_id());
		if (ret_prd_indtList.isEmpty()) {
			msgBuf = "来料箱[" + box.getBox_id() + "]在来料玻璃表中找不到玻璃（retPrdIndt） .";
			return new SimpleRtnBeen(E_RET_PRD_INDT + E_READ_NOT_FOUND, msgBuf);
		}

		// Check
		int i = 0, iMax = prdInfoList.size();
		int j = 0, jMax = ret_prd_indtList.size();
		for (i = 0; i < iMax; i++) {
			for (j = 0; j < jMax; j++) {
				if (prdInfoList
						.get(i)
						.getPrd_seq_id()
						.equals(ret_prd_indtList.get(j).getId()
								.getVdr_prd_seq_id())) {
					if (chkSlot) {
						if (!prdInfoList
								.get(i)
								.getSlot_no()
								.equals(ret_prd_indtList.get(j)
										.getMtrl_slot_no())) {
							// Check slot NG
							msgBuf = "箱子[" + box.getBox_id() + "]中产品 ["
									+ prdInfoList.get(i).getPrd_seq_id()
									+ "]的子格位号为["
									+ prdInfoList.get(i).getSlot_no()
									+ "],和来料箱["
									+ ret_prd_in.getId().getMtrl_box_id()
									+ "]中的子格位号["
									+ ret_prd_indtList.get(j).getMtrl_slot_no()
									+ "]不匹配!";
							return new SimpleRtnBeen(MTRL_PRD_SLOT_CHK_FAILED,
									msgBuf);
						}
					}
					break;// Check slot OK
				} // End of prd id check
			}// End of for prds

			if (j == jMax) {
				// Check failed
				msgBuf = "来料箱[" + ret_prd_in.getId().getMtrl_box_id()
						+ "]中未找到产品[" + prdInfoList.get(i).getPrd_seq_id()
						+ "]的信息";
				return new SimpleRtnBeen(MTRL_PRD_ID_CHK_FAILED, msgBuf);
			}
		}

		return new SimpleRtnBeen(_NORMAL);
	}

	public List<BoxInfoBeen> queryRetBoxInfoBySql(String sql) {
		List<BoxInfoBeen> boxInfoBeenList = new ArrayList<BoxInfoBeen>();
		List<Ret_box> ret_boxList = new ArrayList<Ret_box>();

		ret_boxList = this.find(sql);
		if (!ret_boxList.isEmpty()) {
			for (Ret_box box : ret_boxList) {
				boxInfoBeenList.add(this.retBox2BoxInfo(box));
			}
		}

		return boxInfoBeenList;
	}

	public BoxInfoBeen retBox2BoxInfo(Ret_box ret_box) {
		BoxInfoBeen boxInfoBeen = new BoxInfoBeen();

		Bis_mdl_def bis_mdl_def = new Bis_mdl_def();

		// From Ret_box
		boxInfoBeen.setBox_id(ret_box.getBox_id());
		boxInfoBeen.setBox_stat(ret_box.getBox_stat());
		boxInfoBeen.setLot_id(ret_box.getLot_id_fk());
		boxInfoBeen.setWo_id(ret_box.getWo_id_fk());
		boxInfoBeen.setSo_id(ret_box.getSo_id_fk());
		boxInfoBeen.setPrd_qty(ret_box.getPrd_qty());
		boxInfoBeen.setDest_shop(ret_box.getDest_shop());
		boxInfoBeen.setBox_weight(ret_box.getBox_weight());
		boxInfoBeen.setEvt_cate(ret_box.getEvt_cate());

		// From Ret_wo
		Ret_wo ret_wo = new Ret_wo();
		ret_wo = retWODao.get(ret_box.getWo_id_fk());
		if (ret_wo != null) {
			// TODO Check null
			boxInfoBeen.setMtrl_prod_id(ret_wo.getMtrl_prod_id_fk());// TODO
																		// ret_wo
																		// or
																		// BIS_MTRL?

			if (!Util.isSpaceCheck(ret_wo.getMdl_id_fk())) {
				boxInfoBeen.setMdl_id_fk(ret_wo.getMdl_id_fk());
				bis_mdl_def = bisMdlDefDao.get(ret_wo.getMdl_id_fk());
				boxInfoBeen.setCus_id(bis_mdl_def.getCus_id());
			}
			boxInfoBeen.setFm_mdl_id_fk(ret_wo.getFm_mdl_id_fk());
			boxInfoBeen.setCut_mdl_id_fk(ret_wo.getCut_mdl_id_fk());
			boxInfoBeen.setFrom_thickness(ret_wo.getFrom_thickness());
			boxInfoBeen.setTo_thickness(ret_wo.getTo_thickness());
		}
		return boxInfoBeen;
	}
	
	public List<Ret_box> queryRetBoxBySql(String sql){
		List<Ret_box> Ret_box_list = new ArrayList<Ret_box>();
		Ret_box_list = this.find(sql);
		return Ret_box_list;
	}
	
	public List<BoxInfoBeen> retBox2BoxInfoByManyCondition(String box_id,
			String box_stat, String nx_toolg_id_fk, String wo_id,
			String lot_id_fk) {
		List<BoxInfoBeen> boxInfoBeenList = new ArrayList<BoxInfoBeen>();

		String queryString = "SELECT B.BOX_ID,B.BATCH_NO_FK, B.MDL_ID_FK, B.NX_OPE_ID_FK,B.NX_PATH_ID_FK, "
				+ " B.PRTY,B.PRD_QTY,B.PRD_GRADE_FK,B.LOT_ID_FK,B.EVT_CATE,"
				+ " W.MTRL_PROD_ID_FK,W.FM_MDL_ID_FK,W.CUT_MDL_ID_FK,W.FROM_THICKNESS,W.TO_THICKNESS,"
				+ " M.CUS_ID,"
				+ " L.X_AXIS_CNT,L.Y_AXIS_CNT,B.WO_ID_FK,"
				+ " PG.OK_CNT,PG.NG_CNT,PG.GK_CNT,PG.LZ_CNT"
				+ " FROM RET_BOX B "
				+ " LEFT JOIN ( "
				+ " 	SELECT BOX_ID_FK, "
				+ " 		SUM(CASE WHEN PRD_GRADE='OK' THEN 1 ELSE 0 END ) OK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='NG' THEN 1 ELSE 0 END ) NG_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='GK' THEN 1 ELSE 0 END ) GK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='LZ' THEN 1 ELSE 0 END ) LZ_CNT "
				+ " FROM RET_PRD_INFO "
				+ " WHERE WO_ID_FK = '" + wo_id + "'"
				+ " GROUP BY BOX_ID_FK "
				+ " )PG ON B.BOX_ID = PG.BOX_ID_FK "
				+ " LEFT JOIN RET_WO W  ON B.WO_ID_FK = W.WO_ID"
				+ " LEFT JOIN BIS_MDL_DEF M ON W.MDL_ID_FK = M.MDL_ID"
				+ " LEFT JOIN BIS_LAYOT L ON M.LAYOT_ID_FK = L.LAYOT_ID"
				+ " WHERE B.BOX_STAT='"
				+ box_stat
				+ "' AND B.VALID_FLG='Y' AND B.BNK_FLG='0' ";

		if (!Util.isSpaceCheck(nx_toolg_id_fk)) {
			queryString = queryString + " AND B.NX_TOOLG_ID_FK='"
					+ nx_toolg_id_fk + "'";
		}
		if (!Util.isSpaceCheck(wo_id)) {
			queryString = queryString + " AND B.wo_id_fk='" + wo_id + "'";
		}
		if (!Util.isSpaceCheck(lot_id_fk)) {
			queryString = queryString + " AND B.lot_id_fk='" + lot_id_fk + "'";
		}
		if (!Util.isSpaceCheck(box_id)) {
			queryString = queryString + " AND B.box_id='" + box_id + "'";
		}

		List<Object[]> objList = retPrdInfoDao.findBySQL(queryString);
		for (int i = 0; i < objList.size(); i++) {
			BoxInfoBeen boxInfoBeen = new BoxInfoBeen();
			Object boxInfo[] = objList.get(i);

			boxInfoBeen.setBox_id(boxInfo[0].toString());
			if (boxInfo[2] != null) {
				boxInfoBeen.setMdl_id_fk(boxInfo[2].toString());
			}
			if (boxInfo[3] != null) {
				boxInfoBeen.setNx_ope_id_fk(boxInfo[3].toString());
			}
			if (boxInfo[4] != null) {
				boxInfoBeen.setNx_path_id_fk(boxInfo[4].toString());
			}
			if (boxInfo[5] != null) {
				boxInfoBeen.setPrty(boxInfo[5].toString());
			}
			if (boxInfo[6] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[6].toString()));
			}
			if (boxInfo[7] != null) {
				boxInfoBeen.setPrd_grade_fk(boxInfo[7].toString());
			}
			if (boxInfo[8] != null) {
				boxInfoBeen.setLot_id(boxInfo[8].toString());
			}
			if (boxInfo[9] != null) {
				boxInfoBeen.setEvt_cate(boxInfo[9].toString());
			}
			if (boxInfo[10] != null) {
				boxInfoBeen.setMtrl_prod_id(boxInfo[10].toString());
			}
			if (boxInfo[11] != null) {
				boxInfoBeen.setFm_mdl_id_fk(boxInfo[11].toString());
			}
			if (boxInfo[12] != null) {
				boxInfoBeen.setCut_mdl_id_fk(boxInfo[12].toString());
			}
			if (boxInfo[13] != null) {
				boxInfoBeen.setFrom_thickness(Double.valueOf(boxInfo[13]
						.toString()));
			}
			if (boxInfo[14] != null) {
				boxInfoBeen.setTo_thickness(Double.valueOf(boxInfo[14]
						.toString()));
			}
			if (boxInfo[15] != null) {
				boxInfoBeen.setCus_id(boxInfo[15].toString());
			}
			if (boxInfo[16] != null && boxInfo[17] != null) {
				boxInfoBeen.setMode_cnt(Integer.valueOf(boxInfo[16].toString())
						* Integer.valueOf(boxInfo[17].toString()));
			} else {
				boxInfoBeen.setMode_cnt(0);
			}
			if (boxInfo[18] != null) {
				boxInfoBeen.setWo_id(boxInfo[18].toString());
			}

			if (boxInfo[19] != null) {
				boxInfoBeen.setOk_cnt(Integer.valueOf(boxInfo[19].toString()));
			} else {
				boxInfoBeen.setOk_cnt(0);
			}
			if (boxInfo[20] != null) {
				boxInfoBeen.setNg_cnt(Integer.valueOf(boxInfo[20].toString()));
			} else {
				boxInfoBeen.setNg_cnt(0);
			}
			if (boxInfo[21] != null) {
				boxInfoBeen.setGk_cnt(Integer.valueOf(boxInfo[21].toString()));
			} else {
				boxInfoBeen.setGk_cnt(0);
			}
			if (boxInfo[22] != null) {
				boxInfoBeen.setLz_cnt(Integer.valueOf(boxInfo[22].toString()));
			} else {
				boxInfoBeen.setLz_cnt(0);
			}

			boxInfoBeenList.add(boxInfoBeen);
		}

		return boxInfoBeenList;
	}

	public List<BoxInfoBeen> retBox2BoxInfoByManyConditionWH(String box_id,
			String box_stat, String wo_id, String wo_ids, String dest_shop,
			String bnk_flg, String cus_id, String bnk_flgs ,String so_id) {
		List<BoxInfoBeen> boxInfoBeenList = new ArrayList<BoxInfoBeen>();

		String queryCondition = "1=1";

		if (!Util.isSpaceCheck(box_id)) {
			queryCondition += " AND B.BOX_ID='" + box_id + "'";
		}
		if (!Util.isSpaceCheck(box_stat)) {
			queryCondition += " AND B.BOX_STAT='" + box_stat + "'";
		}
		if (!Util.isSpaceCheck(wo_ids)) {
			queryCondition += " AND B.WO_ID_FK IN(" + wo_ids + ")";
		} else if (!Util.isSpaceCheck(wo_id)) {
			queryCondition += " AND B.WO_ID_FK='" + wo_id + "'";
		}
		if (!Util.isSpaceCheck(dest_shop)) {
			queryCondition += " AND B.DEST_SHOP='" + dest_shop + "'";
		}
		if (!Util.isSpaceCheck(bnk_flg)) {
			queryCondition += " AND B.BNK_FLG='" + bnk_flg + "'";
		} else if (!Util.isSpaceCheck(bnk_flgs)) {
			queryCondition += " AND B.BNK_FLG IN(" + bnk_flgs + ")";
		}
		if (!Util.isSpaceCheck(cus_id)) {
			queryCondition += " AND W.CUS_ID_FK='" + cus_id + "'";
		}
        if (!Util.isSpaceCheck(so_id)){
        	queryCondition += " AND B.SO_ID_FK='" + so_id + "'";
        }
		String queryString = " SELECT "
				+ " B.BOX_ID,B.BATCH_NO_FK, B.MDL_ID_FK, B.NX_OPE_ID_FK,B.NX_PATH_ID_FK, B.PRTY,B.PRD_QTY,B.PRD_GRADE_FK,B.LOT_ID_FK,B.BNK_FLG,"// 0~9
				+ " W.MTRL_PROD_ID_FK,W.FM_MDL_ID_FK,W.CUT_MDL_ID_FK,W.FROM_THICKNESS,W.TO_THICKNESS, W.CUS_ID_FK, L.X_AXIS_CNT,L.Y_AXIS_CNT, B.WO_ID_FK, "// 10~18
				+ " B.BOX_STAT,B.DEST_SHOP,B.EVT_USR,B.EVT_TIMESTAMP,B.BOX_FST_YIELD,B.BOX_SND_YIELD,B.SO_ID_FK,"// ->25
				+ " PG.OK_CNT,PG.NG_CNT,PG.GK_CNT,PG.LZ_CNT,B.PRD_ADMIN_FLG,PG.SC_CNT,B.WH_IN_TIMESTAMP,B.RELATE_USR"
				+ " FROM RET_BOX AS B"
				+ " LEFT JOIN ( "
				+ " 	SELECT BOX_ID_FK, "
				+ " 		SUM(CASE WHEN PRD_GRADE='OK' THEN 1 ELSE 0 END ) OK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='NG' THEN 1 ELSE 0 END ) NG_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='GK' THEN 1 ELSE 0 END ) GK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='LZ' THEN 1 ELSE 0 END ) LZ_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='SC' THEN 1 ELSE 0 END ) SC_CNT "
				+ " 	FROM RET_PRD_INFO ";
		if (!Util.isSpaceCheck(wo_ids)) {
			queryString += " WHERE WO_ID_FK IN(" + wo_ids + ")";
		} else if (!Util.isSpaceCheck(wo_id)) {
			queryString += " WHERE WO_ID_FK='" + wo_id + "'";
		}
		queryString += " GROUP BY BOX_ID_FK "
				+ " )PG ON B.BOX_ID = PG.BOX_ID_FK "
				+ " LEFT JOIN RET_WO W  ON B.WO_ID_FK = W.WO_ID"
				+ " LEFT JOIN BIS_MDL_DEF M ON W.MDL_ID_FK = M.MDL_ID"
				+ " LEFT JOIN BIS_LAYOT L ON M.LAYOT_ID_FK = L.LAYOT_ID"
				+ " WHERE  " + queryCondition + " GROUP BY B.BOX_ID ";

		List<Object[]> objList = retPrdInfoDao.findBySQL(queryString);
		for (int i = 0; i < objList.size(); i++) {
			BoxInfoBeen boxInfoBeen = new BoxInfoBeen();
			Object boxInfo[] = objList.get(i);

			boxInfoBeen.setBox_id(boxInfo[0].toString());
			if (boxInfo[2] != null) {
				boxInfoBeen.setMdl_id_fk(boxInfo[2].toString());
			}
			if (boxInfo[3] != null) {
				boxInfoBeen.setNx_ope_id_fk(boxInfo[3].toString());
			}
			if (boxInfo[4] != null) {
				boxInfoBeen.setNx_path_id_fk(boxInfo[4].toString());
			}
			if (boxInfo[5] != null) {
				boxInfoBeen.setPrty(boxInfo[5].toString());
			}
			if (boxInfo[6] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[6].toString()));
			} else {
				boxInfoBeen.setPrd_qty(0);
			}
			if (boxInfo[7] != null) {
				boxInfoBeen.setPrd_grade_fk(boxInfo[7].toString());
			}
			if (boxInfo[8] != null) {
				boxInfoBeen.setLot_id(boxInfo[8].toString());
			}
			if (boxInfo[9] != null) {
				boxInfoBeen.setBnk_flg(boxInfo[9].toString());
				if ("2".equals(boxInfo[9].toString())) {
					boxInfoBeen.setIs_wip_in(_YES);
				} else {
					boxInfoBeen.setIs_wip_in(_NO);
				}
			}
			if (boxInfo[10] != null) {
				boxInfoBeen.setMtrl_prod_id(boxInfo[10].toString());
			}
			if (boxInfo[11] != null) {
				boxInfoBeen.setFm_mdl_id_fk(boxInfo[11].toString());
			}
			if (boxInfo[12] != null) {
				boxInfoBeen.setCut_mdl_id_fk(boxInfo[12].toString());
			}
			if (boxInfo[13] != null) {
				boxInfoBeen.setFrom_thickness(Double.valueOf(boxInfo[13]
						.toString()));
			}
			if (boxInfo[14] != null) {
				boxInfoBeen.setTo_thickness(Double.valueOf(boxInfo[14]
						.toString()));
			}
			if (boxInfo[15] != null) {
				boxInfoBeen.setCus_id(boxInfo[15].toString());
			}
			if (boxInfo[16] != null && boxInfo[17] != null) {
				boxInfoBeen.setMode_cnt(Integer.valueOf(boxInfo[16].toString())
						* Integer.valueOf(boxInfo[17].toString()));
			} else {
				boxInfoBeen.setMode_cnt(0);
			}
			if (boxInfo[18] != null) {
				boxInfoBeen.setWo_id(boxInfo[18].toString());
			}

			if (boxInfo[19] != null) {
				boxInfoBeen.setBox_stat(boxInfo[19].toString());
			}

			if (boxInfo[20] != null) {
				boxInfoBeen.setDest_shop(boxInfo[20].toString());
				boxInfoBeen.setDest_shop_dsc(boxInfo[20].toString());
			}
			if (boxInfo[21] != null) {
				boxInfoBeen.setEvt_usr(boxInfo[21].toString());
			}
			if (boxInfo[22] != null) {
				boxInfoBeen.setEvt_timestamp(boxInfo[22].toString());
			}
			if (boxInfo[23] != null) {
				boxInfoBeen.setBox_fst_yield(boxInfo[23].toString() + "%");
			}
			if (boxInfo[24] != null) {
				boxInfoBeen.setBox_snd_yield(boxInfo[24].toString() + "%");
			}
			if (boxInfo[25] != null) {
				boxInfoBeen.setSo_id(boxInfo[25].toString());
			}

			if (boxInfo[26] != null) {
				boxInfoBeen.setOk_cnt(Integer.valueOf(boxInfo[26].toString()));
			} else {
				boxInfoBeen.setOk_cnt(0);
			}
			if (boxInfo[27] != null) {
				boxInfoBeen.setNg_cnt(Integer.valueOf(boxInfo[27].toString()));
			} else {
				boxInfoBeen.setNg_cnt(0);
			}
			if (boxInfo[28] != null) {
				boxInfoBeen.setGk_cnt(Integer.valueOf(boxInfo[28].toString()));
			} else {
				boxInfoBeen.setGk_cnt(0);
			}
			if (boxInfo[29] != null) {
				boxInfoBeen.setLz_cnt(Integer.valueOf(boxInfo[29].toString()));
			} else {
				boxInfoBeen.setLz_cnt(0);
			}
			if (boxInfo[30] != null){
				boxInfoBeen.setPrd_admin_flg(boxInfo[30].toString());
			}
			if (boxInfo[31] != null) {
				boxInfoBeen.setSc_cnt(Integer.valueOf(boxInfo[31].toString()));
			} else {
				boxInfoBeen.setSc_cnt(0);
			}
			if (boxInfo[32] != null){
				boxInfoBeen.setWh_in_timestamp(boxInfo[32].toString());
			}
			if (boxInfo[33] != null){
				boxInfoBeen.setRelate_usr(boxInfo[33].toString());
			}
			boxInfoBeenList.add(boxInfoBeen);
		}

		return boxInfoBeenList;
	}
	public List<BoxInfoBeen> retBox2BoxInfoByManyConditionWHwithFAB(String box_id,
			String box_stat, String wo_id, String wo_ids, String dest_shop,
			String bnk_flg, String cus_id, String so_id) {
		List<BoxInfoBeen> boxInfoBeenList = new ArrayList<BoxInfoBeen>();

		String queryCondition = "1=1";

		if (!Util.isSpaceCheck(box_id)) {
			queryCondition += " AND B.BOX_ID='" + box_id + "'";
		}
		if (!Util.isSpaceCheck(box_stat)) {
			queryCondition += " AND B.BOX_STAT='" + box_stat + "'";
		}
		if (!Util.isSpaceCheck(wo_ids)) {
			queryCondition += " AND B.WO_ID_FK IN(" + wo_ids + ")";
		} else if (!Util.isSpaceCheck(wo_id)) {
			queryCondition += " AND B.WO_ID_FK='" + wo_id + "'";
		}
		if (!Util.isSpaceCheck(dest_shop)) {
			queryCondition += " AND B.DEST_SHOP='" + dest_shop + "'";
		}
		if (!Util.isSpaceCheck(bnk_flg)) {
			queryCondition += " AND B.BNK_FLG='" + bnk_flg + "'";
		} 
		if (!Util.isSpaceCheck(cus_id)) {
			queryCondition += " AND W.CUS_ID_FK='" + cus_id + "'";
		}
        if (!Util.isSpaceCheck(so_id)){
        	queryCondition += " AND B.SO_ID_FK='" + so_id + "'";
        }

		String queryString = " SELECT "
				+ " B.BOX_ID,B.BATCH_NO_FK, B.MDL_ID_FK, B.NX_OPE_ID_FK,B.NX_PATH_ID_FK, B.PRTY,B.PRD_QTY,B.PRD_GRADE_FK,B.LOT_ID_FK,B.BNK_FLG,"// 0~9
				+ " W.MTRL_PROD_ID_FK,W.FM_MDL_ID_FK,W.CUT_MDL_ID_FK,W.FROM_THICKNESS,W.TO_THICKNESS, W.CUS_ID_FK, L.X_AXIS_CNT,L.Y_AXIS_CNT, B.WO_ID_FK, "// 10~18
				+ " B.BOX_STAT,B.DEST_SHOP,B.EVT_USR,B.EVT_TIMESTAMP,B.BOX_FST_YIELD,B.BOX_SND_YIELD,B.SO_ID_FK,"// ->25
				+ " PG.OK_CNT,PG.NG_CNT,PG.GK_CNT,PG.LZ_CNT,B.PRD_ADMIN_FLG,PG.SC_CNT,B.WH_IN_TIMESTAMP,B.RELATE_USR,B.SHIP_BOX_ID"
				+ " FROM RET_BOX AS B"
				+ " INNER JOIN ( "
				+ " 	SELECT BOX_ID_FK, "
				+ " 		SUM(CASE WHEN PRD_GRADE='OK' THEN 1 ELSE 0 END ) OK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='NG' THEN 1 ELSE 0 END ) NG_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='GK' THEN 1 ELSE 0 END ) GK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='LZ' THEN 1 ELSE 0 END ) LZ_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='SC' THEN 1 ELSE 0 END ) SC_CNT ";
		queryString += " FROM RET_PRD_INFO GROUP BY BOX_ID_FK ";
		queryString += " )PG ON B.BOX_ID = PG.BOX_ID_FK "
				+ " LEFT JOIN RET_WO W  ON B.WO_ID_FK = W.WO_ID"
				+ " LEFT JOIN BIS_MDL_DEF M ON W.MDL_ID_FK = M.MDL_ID"
				+ " LEFT JOIN BIS_LAYOT L ON M.LAYOT_ID_FK = L.LAYOT_ID"
				+ " WHERE  " + queryCondition + " GROUP BY B.BOX_ID ";

		List<Object[]> objList = retPrdInfoDao.findBySQL(queryString);
		for (int i = 0; i < objList.size(); i++) {
			BoxInfoBeen boxInfoBeen = new BoxInfoBeen();
			Object boxInfo[] = objList.get(i);

			boxInfoBeen.setBox_id(boxInfo[0].toString());
			if (boxInfo[2] != null) {
				boxInfoBeen.setMdl_id_fk(boxInfo[2].toString());
			}
			if (boxInfo[3] != null) {
				boxInfoBeen.setNx_ope_id_fk(boxInfo[3].toString());
			}
			if (boxInfo[4] != null) {
				boxInfoBeen.setNx_path_id_fk(boxInfo[4].toString());
			}
			if (boxInfo[5] != null) {
				boxInfoBeen.setPrty(boxInfo[5].toString());
			}
			if (boxInfo[6] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[6].toString()));
			} else {
				boxInfoBeen.setPrd_qty(0);
			}
			if (boxInfo[7] != null) {
				boxInfoBeen.setPrd_grade_fk(boxInfo[7].toString());
			}
			if (boxInfo[8] != null) {
				boxInfoBeen.setLot_id(boxInfo[8].toString());
			}
			if (boxInfo[9] != null) {
				boxInfoBeen.setBnk_flg(boxInfo[9].toString());
				if ("2".equals(boxInfo[9].toString())) {
					boxInfoBeen.setIs_wip_in(_YES);
				} else {
					boxInfoBeen.setIs_wip_in(_NO);
				}
			}
			if (boxInfo[10] != null) {
				boxInfoBeen.setMtrl_prod_id(boxInfo[10].toString());
			}
			if (boxInfo[11] != null) {
				boxInfoBeen.setFm_mdl_id_fk(boxInfo[11].toString());
			}
			if (boxInfo[12] != null) {
				boxInfoBeen.setCut_mdl_id_fk(boxInfo[12].toString());
			}
			if (boxInfo[13] != null) {
				boxInfoBeen.setFrom_thickness(Double.valueOf(boxInfo[13]
						.toString()));
			}
			if (boxInfo[14] != null) {
				boxInfoBeen.setTo_thickness(Double.valueOf(boxInfo[14]
						.toString()));
			}
			if (boxInfo[15] != null) {
				boxInfoBeen.setCus_id(boxInfo[15].toString());
			}
			if (boxInfo[16] != null && boxInfo[17] != null) {
				boxInfoBeen.setMode_cnt(Integer.valueOf(boxInfo[16].toString())
						* Integer.valueOf(boxInfo[17].toString()));
			} else {
				boxInfoBeen.setMode_cnt(0);
			}
			if (boxInfo[18] != null) {
				boxInfoBeen.setWo_id(boxInfo[18].toString());
			}

			if (boxInfo[19] != null) {
				boxInfoBeen.setBox_stat(boxInfo[19].toString());
			}

			if (boxInfo[20] != null) {
				boxInfoBeen.setDest_shop(boxInfo[20].toString());
				boxInfoBeen.setDest_shop_dsc(boxInfo[20].toString());
			}
			if (boxInfo[21] != null) {
				boxInfoBeen.setEvt_usr(boxInfo[21].toString());
			}
			if (boxInfo[22] != null) {
				boxInfoBeen.setEvt_timestamp(boxInfo[22].toString());
			}
			if (boxInfo[23] != null) {
				boxInfoBeen.setBox_fst_yield(boxInfo[23].toString() + "%");
			}
			if (boxInfo[24] != null) {
				boxInfoBeen.setBox_snd_yield(boxInfo[24].toString() + "%");
			}
			if (boxInfo[25] != null) {
				boxInfoBeen.setSo_id(boxInfo[25].toString());
			}

			if (boxInfo[26] != null) {
				boxInfoBeen.setOk_cnt(Integer.valueOf(boxInfo[26].toString()));
			} else {
				boxInfoBeen.setOk_cnt(0);
			}
			if (boxInfo[27] != null) {
				boxInfoBeen.setNg_cnt(Integer.valueOf(boxInfo[27].toString()));
			} else {
				boxInfoBeen.setNg_cnt(0);
			}
			if (boxInfo[28] != null) {
				boxInfoBeen.setGk_cnt(Integer.valueOf(boxInfo[28].toString()));
			} else {
				boxInfoBeen.setGk_cnt(0);
			}
			if (boxInfo[29] != null) {
				boxInfoBeen.setLz_cnt(Integer.valueOf(boxInfo[29].toString()));
			} else {
				boxInfoBeen.setLz_cnt(0);
			}
			if (boxInfo[30] != null){
				boxInfoBeen.setPrd_admin_flg(boxInfo[30].toString());
			}
			if (boxInfo[31] != null) {
				boxInfoBeen.setSc_cnt(Integer.valueOf(boxInfo[31].toString()));
			} else {
				boxInfoBeen.setSc_cnt(0);
			}
			if (boxInfo[32] != null){
				boxInfoBeen.setWh_in_timestamp(boxInfo[32].toString());
			}
			if (boxInfo[33] != null){
				boxInfoBeen.setRelate_usr(boxInfo[33].toString());
			}
			if (boxInfo[34] != null){
				boxInfoBeen.setShip_box_id(boxInfo[34].toString());
			}
			boxInfoBeenList.add(boxInfoBeen);
		}

		return boxInfoBeenList;
	}
	

	public List<BoxInfoBeen> retBox2BoxInfoByManyConditionWHwithSHOP(String box_id,
			String box_stat, String wo_id, String wo_ids, String dest_shop,
			String bnk_flg, String cus_id, String so_id) {
		List<BoxInfoBeen> boxInfoBeenList = new ArrayList<BoxInfoBeen>();

		String queryCondition = "1=1";

		if (!Util.isSpaceCheck(box_id)) {
			queryCondition += " AND B.BOX_ID='" + box_id + "'";
		}
		if (!Util.isSpaceCheck(box_stat)) {
			queryCondition += " AND B.BOX_STAT='" + box_stat + "'";
		}
		if (!Util.isSpaceCheck(wo_ids)) {
			queryCondition += " AND B.WO_ID_FK IN(" + wo_ids + ")";
		} else if (!Util.isSpaceCheck(wo_id)) {
			queryCondition += " AND B.WO_ID_FK='" + wo_id + "'";
		}
		if (!Util.isSpaceCheck(dest_shop)) {
			queryCondition += " AND B.DEST_SHOP='" + dest_shop + "'";
		}
		if (!Util.isSpaceCheck(bnk_flg)) {
			queryCondition += " AND B.BNK_FLG='" + bnk_flg + "'";
		} 
		if (!Util.isSpaceCheck(cus_id)) {
			queryCondition += " AND W.CUS_ID_FK='" + cus_id + "'";
		}
        if (!Util.isSpaceCheck(so_id)){
        	queryCondition += " AND B.SO_ID_FK='" + so_id + "'";
        }

		String queryString = " SELECT "
				+ " B.BOX_ID,B.BATCH_NO_FK, B.MDL_ID_FK, B.NX_OPE_ID_FK,B.NX_PATH_ID_FK, B.PRTY,B.PRD_QTY,B.PRD_GRADE_FK,B.LOT_ID_FK,B.BNK_FLG,"// 0~9
				+ " W.MTRL_PROD_ID_FK,W.FM_MDL_ID_FK,W.CUT_MDL_ID_FK,W.FROM_THICKNESS,W.TO_THICKNESS, W.CUS_ID_FK, L.X_AXIS_CNT,L.Y_AXIS_CNT, B.WO_ID_FK, "// 10~18
				+ " B.BOX_STAT,B.DEST_SHOP,B.EVT_USR,B.EVT_TIMESTAMP,B.BOX_FST_YIELD,B.BOX_SND_YIELD,B.SO_ID_FK,"// ->25
				+ " PG.OK_CNT,PG.NG_CNT,PG.GK_CNT,PG.LZ_CNT,B.PRD_ADMIN_FLG,PG.SC_CNT,B.WH_IN_TIMESTAMP,B.RELATE_USR,B.PALLET_ID_FK,B.EXT_11,W.TH_MDL_ID_FK,B.SHIP_BOX_ID"
				+ " FROM RET_BOX AS B"
				+ " INNER JOIN ( "
				+ " 	SELECT BOX_ID_FK, "
				+ " 		SUM(CASE WHEN PRD_GRADE='OK' THEN 1 ELSE 0 END ) OK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='NG' THEN 1 ELSE 0 END ) NG_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='GK' THEN 1 ELSE 0 END ) GK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='LZ' THEN 1 ELSE 0 END ) LZ_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='SC' THEN 1 ELSE 0 END ) SC_CNT "
			    + " FROM RET_PRD_INFO ";
		if (!Util.isSpaceCheck(wo_ids)) {
			queryString += " WHERE WO_ID_FK IN(" + wo_ids + ")";
		} else if (!Util.isSpaceCheck(wo_id)) {
			queryString += " WHERE WO_ID_FK='" + wo_id + "'";
		}
		queryString += " GROUP BY BOX_ID_FK "
			    + " )PG ON B.BOX_ID = PG.BOX_ID_FK "
				+ " LEFT JOIN RET_WO W  ON B.WO_ID_FK = W.WO_ID"
				+ " LEFT JOIN BIS_MDL_DEF M ON W.MDL_ID_FK = M.MDL_ID"
				+ " LEFT JOIN BIS_LAYOT L ON M.LAYOT_ID_FK = L.LAYOT_ID"
				+ " WHERE  " + queryCondition + " GROUP BY B.BOX_ID ";

		List<Object[]> objList = retPrdInfoDao.findBySQL(queryString);
		for (int i = 0; i < objList.size(); i++) {
			BoxInfoBeen boxInfoBeen = new BoxInfoBeen();
			Object boxInfo[] = objList.get(i);

			boxInfoBeen.setBox_id(boxInfo[0].toString());
			if (boxInfo[2] != null) {
				boxInfoBeen.setMdl_id_fk(boxInfo[2].toString());
			}
			if (boxInfo[3] != null) {
				boxInfoBeen.setNx_ope_id_fk(boxInfo[3].toString());
			}
			if (boxInfo[4] != null) {
				boxInfoBeen.setNx_path_id_fk(boxInfo[4].toString());
			}
			if (boxInfo[5] != null) {
				boxInfoBeen.setPrty(boxInfo[5].toString());
			}
			if (boxInfo[6] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[6].toString()));
			} else {
				boxInfoBeen.setPrd_qty(0);
			}
			if (boxInfo[7] != null) {
				boxInfoBeen.setPrd_grade_fk(boxInfo[7].toString());
			}
			if (boxInfo[8] != null) {
				boxInfoBeen.setLot_id(boxInfo[8].toString());
			}
			if (boxInfo[9] != null) {
				boxInfoBeen.setBnk_flg(boxInfo[9].toString());
				if ("2".equals(boxInfo[9].toString())) {
					boxInfoBeen.setIs_wip_in(_YES);
				} else {
					boxInfoBeen.setIs_wip_in(_NO);
				}
			}
			if (boxInfo[10] != null) {
				boxInfoBeen.setMtrl_prod_id(boxInfo[10].toString());
			}
			if (boxInfo[11] != null) {
				boxInfoBeen.setFm_mdl_id_fk(boxInfo[11].toString());
			}
			if (boxInfo[12] != null) {
				boxInfoBeen.setCut_mdl_id_fk(boxInfo[12].toString());
			}
			if (boxInfo[13] != null) {
				boxInfoBeen.setFrom_thickness(Double.valueOf(boxInfo[13]
						.toString()));
			}
			if (boxInfo[14] != null) {
				boxInfoBeen.setTo_thickness(Double.valueOf(boxInfo[14]
						.toString()));
			}
			if (boxInfo[15] != null) {
				boxInfoBeen.setCus_id(boxInfo[15].toString());
			}
			if (boxInfo[16] != null && boxInfo[17] != null) {
				boxInfoBeen.setMode_cnt(Integer.valueOf(boxInfo[16].toString())
						* Integer.valueOf(boxInfo[17].toString()));
			} else {
				boxInfoBeen.setMode_cnt(0);
			}
			if (boxInfo[18] != null) {
				boxInfoBeen.setWo_id(boxInfo[18].toString());
			}

			if (boxInfo[19] != null) {
				boxInfoBeen.setBox_stat(boxInfo[19].toString());
			}

			if (boxInfo[20] != null) {
				boxInfoBeen.setDest_shop(boxInfo[20].toString());
				boxInfoBeen.setDest_shop_dsc(boxInfo[20].toString());
			}
			if (boxInfo[21] != null) {
				boxInfoBeen.setEvt_usr(boxInfo[21].toString());
			}
			if (boxInfo[22] != null) {
				boxInfoBeen.setEvt_timestamp(boxInfo[22].toString());
			}
			if (boxInfo[23] != null) {
				boxInfoBeen.setBox_fst_yield(boxInfo[23].toString() + "%");
			}
			if (boxInfo[24] != null) {
				boxInfoBeen.setBox_snd_yield(boxInfo[24].toString() + "%");
			}
			if (boxInfo[25] != null) {
				boxInfoBeen.setSo_id(boxInfo[25].toString());
			}

			if (boxInfo[26] != null) {
				boxInfoBeen.setOk_cnt(Integer.valueOf(boxInfo[26].toString()));
			} else {
				boxInfoBeen.setOk_cnt(0);
			}
			if (boxInfo[27] != null) {
				boxInfoBeen.setNg_cnt(Integer.valueOf(boxInfo[27].toString()));
			} else {
				boxInfoBeen.setNg_cnt(0);
			}
			if (boxInfo[28] != null) {
				boxInfoBeen.setGk_cnt(Integer.valueOf(boxInfo[28].toString()));
			} else {
				boxInfoBeen.setGk_cnt(0);
			}
			if (boxInfo[29] != null) {
				boxInfoBeen.setLz_cnt(Integer.valueOf(boxInfo[29].toString()));
			} else {
				boxInfoBeen.setLz_cnt(0);
			}
			if (boxInfo[30] != null){
				boxInfoBeen.setPrd_admin_flg(boxInfo[30].toString());
			}
			if (boxInfo[31] != null) {
				boxInfoBeen.setSc_cnt(Integer.valueOf(boxInfo[31].toString()));
			} else {
				boxInfoBeen.setSc_cnt(0);
			}
			if (boxInfo[32] != null){
				boxInfoBeen.setWh_in_timestamp(boxInfo[32].toString());
			}
			if (boxInfo[33] != null){
				boxInfoBeen.setRelate_usr(boxInfo[33].toString());
			}
			if (boxInfo[34] != null){
				boxInfoBeen.setPallet_id_fk(boxInfo[34].toString());
			}
			if (boxInfo[35] != null){
				boxInfoBeen.setExt_11(boxInfo[35].toString());
			}
			if (boxInfo[36] != null){
				boxInfoBeen.setTh_mdl_id_fk(boxInfo[36].toString());
			}
			if(!Util.isSpaceCheck(boxInfoBeen.getCut_mdl_id_fk())){
				boxInfoBeen.setFinal_mdl(boxInfoBeen.getCut_mdl_id_fk());
			}else if(!Util.isSpaceCheck(boxInfoBeen.getFm_mdl_id_fk())){
				boxInfoBeen.setFinal_mdl(boxInfoBeen.getFm_mdl_id_fk());
			}else{
				boxInfoBeen.setFinal_mdl(boxInfoBeen.getTh_mdl_id_fk());
			}
			if (boxInfo[37] != null){
				boxInfoBeen.setShip_box_id(boxInfo[37].toString());
			}
			boxInfoBeenList.add(boxInfoBeen);
		}

		return boxInfoBeenList;
	}	
	
	public List<BoxInfoBeen> retBox2BoxInfoByManyConditionWipWH(String box_id,
			String box_stat, String wo_id, String dest_shop, String bnk_flg,
			String nx_ope_id) {
		List<BoxInfoBeen> boxInfoBeenList = new ArrayList<BoxInfoBeen>();

		String queryCondition = "1=1";

		if (!Util.isSpaceCheck(box_id)) {
			queryCondition += " AND B.BOX_ID='" + box_id + "'";
		}
		if (!Util.isSpaceCheck(box_stat)) {
			queryCondition += " AND B.BOX_STAT='" + box_stat + "'";
		}
		if (!Util.isSpaceCheck(wo_id)) {
			queryCondition += " AND B.WO_ID_FK='" + wo_id + "'";
		}
		if (!Util.isSpaceCheck(dest_shop)) {
			queryCondition += " AND B.DEST_SHOP='" + dest_shop + "'";
		}
		if (!Util.isSpaceCheck(bnk_flg)) {
			queryCondition += " AND B.BNK_FLG='" + bnk_flg + "'";
		}
		if (!Util.isSpaceCheck(nx_ope_id)) {
			queryCondition += " AND B.NX_OPE_ID_FK='" + nx_ope_id + "'";
		}

		String queryString = " SELECT "
				+ " B.BOX_ID,B.BATCH_NO_FK, B.MDL_ID_FK, B.NX_OPE_ID_FK,B.NX_PATH_ID_FK, B.PRTY,B.PRD_QTY,B.PRD_GRADE_FK,B.LOT_ID_FK,B.BNK_FLG,"
				+ " W.MTRL_PROD_ID_FK,W.FM_MDL_ID_FK,W.CUT_MDL_ID_FK,W.FROM_THICKNESS,W.TO_THICKNESS, W.CUS_ID_FK, L.X_AXIS_CNT,L.Y_AXIS_CNT, B.WO_ID_FK, "
				+ " 	B.BOX_STAT,B.PRD_QTY,B.DEST_SHOP,B.EVT_USR,B.EVT_TIMESTAMP,B.SO_ID_FK,B.PRD_ADMIN_FLG,"
				+ " PG.OK_CNT,PG.NG_CNT,PG.GK_CNT,PG.LZ_CNT,PG.SC_CNT,B.WH_IN_TIMESTAMP"
				+ " FROM RET_BOX AS B"
				+ " LEFT JOIN ( "
				+ " 	SELECT BOX_ID_FK, "
				+ " 		SUM(CASE WHEN PRD_GRADE='OK' THEN 1 ELSE 0 END ) OK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='NG' THEN 1 ELSE 0 END ) NG_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='GK' THEN 1 ELSE 0 END ) GK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='LZ' THEN 1 ELSE 0 END ) LZ_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='SC' THEN 1 ELSE 0 END ) SC_CNT "
				+ " 	FROM RET_PRD_INFO WHERE WO_ID_FK='" + wo_id + "'"
				+ " GROUP BY BOX_ID_FK "
				+ " )PG ON B.BOX_ID = PG.BOX_ID_FK "
				+ " LEFT JOIN RET_WO W  ON B.WO_ID_FK = W.WO_ID"
				+ " LEFT JOIN BIS_MDL_DEF M ON W.MDL_ID_FK = M.MDL_ID"
				+ " LEFT JOIN BIS_LAYOT L ON M.LAYOT_ID_FK = L.LAYOT_ID"
				+ " WHERE  " + queryCondition + " GROUP BY B.BOX_ID ";

		List<Object[]> objList = retPrdInfoDao.findBySQL(queryString);
		for (int i = 0; i < objList.size(); i++) {
			BoxInfoBeen boxInfoBeen = new BoxInfoBeen();
			Object boxInfo[] = objList.get(i);

			boxInfoBeen.setBox_id(boxInfo[0].toString());
			if (boxInfo[2] != null) {
				boxInfoBeen.setMdl_id_fk(boxInfo[2].toString());
			}
			if (boxInfo[3] != null) {
				boxInfoBeen.setNx_ope_id_fk(boxInfo[3].toString());
			}
			if (boxInfo[4] != null) {
				boxInfoBeen.setNx_path_id_fk(boxInfo[4].toString());
			}
			if (boxInfo[5] != null) {
				boxInfoBeen.setPrty(boxInfo[5].toString());
			}
			if (boxInfo[6] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[6].toString()));
			}
			if (boxInfo[7] != null) {
				boxInfoBeen.setPrd_grade_fk(boxInfo[7].toString());
			}
			if (boxInfo[8] != null) {
				boxInfoBeen.setLot_id(boxInfo[8].toString());
			}
			if (boxInfo[9] != null) {
				boxInfoBeen.setBnk_flg(boxInfo[9].toString());
			}
			if (boxInfo[10] != null) {
				boxInfoBeen.setMtrl_prod_id(boxInfo[10].toString());
			}
			if (boxInfo[11] != null) {
				boxInfoBeen.setFm_mdl_id_fk(boxInfo[11].toString());
			}
			if (boxInfo[12] != null) {
				boxInfoBeen.setCut_mdl_id_fk(boxInfo[12].toString());
			}
			if (boxInfo[13] != null) {
				boxInfoBeen.setFrom_thickness(Double.valueOf(boxInfo[13]
						.toString()));
			}
			if (boxInfo[14] != null) {
				boxInfoBeen.setTo_thickness(Double.valueOf(boxInfo[14]
						.toString()));
			}
			if (boxInfo[15] != null) {
				boxInfoBeen.setCus_id(boxInfo[15].toString());
			}
			if (boxInfo[16] != null && boxInfo[17] != null) {
				boxInfoBeen.setMode_cnt(Integer.valueOf(boxInfo[16].toString())
						* Integer.valueOf(boxInfo[17].toString()));
			} else {
				boxInfoBeen.setMode_cnt(0);
			}
			if (boxInfo[18] != null) {
				boxInfoBeen.setWo_id(boxInfo[18].toString());
			}

			if (boxInfo[19] != null) {
				boxInfoBeen.setBox_stat(boxInfo[19].toString());
			}
			if (boxInfo[20] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[20].toString()));
			} else {
				boxInfoBeen.setPrd_qty(0);
			}
			if (boxInfo[21] != null) {
				boxInfoBeen.setDest_shop(boxInfo[21].toString());
				boxInfoBeen.setDest_shop_dsc(boxInfo[21].toString());
			}
			if (boxInfo[22] != null) {
				boxInfoBeen.setEvt_usr(boxInfo[22].toString());
			}
			if (boxInfo[23] != null) {
				boxInfoBeen.setEvt_timestamp(boxInfo[23].toString());
			}
			if (boxInfo[24] != null) {
				boxInfoBeen.setSo_id(boxInfo[24].toString());
			}
			if (boxInfo[25] != null){
				if (boxInfo[25].toString().substring(0, 1).equals(_YES)){
					boxInfoBeen.setPrd_admin_flg("Y");
				}else {
					boxInfoBeen.setPrd_admin_flg("N");
				}
			}
			if (boxInfo[26] != null) {
				boxInfoBeen.setOk_cnt(Integer.valueOf(boxInfo[26].toString()));
			} else {
				boxInfoBeen.setOk_cnt(0);
			}
			if (boxInfo[27] != null) {
				boxInfoBeen.setNg_cnt(Integer.valueOf(boxInfo[27].toString()));
			} else {
				boxInfoBeen.setNg_cnt(0);
			}
			if (boxInfo[28] != null) {
				boxInfoBeen.setGk_cnt(Integer.valueOf(boxInfo[28].toString()));
			} else {
				boxInfoBeen.setGk_cnt(0);
			}
			if (boxInfo[29] != null) {
				boxInfoBeen.setLz_cnt(Integer.valueOf(boxInfo[29].toString()));
			} else {
				boxInfoBeen.setLz_cnt(0);
			}
			if (boxInfo[30] != null) {
				boxInfoBeen.setSc_cnt(Integer.valueOf(boxInfo[30].toString()));
			} else {
				boxInfoBeen.setSc_cnt(0);
			}
			if (boxInfo[31] != null) {
				boxInfoBeen.setWh_in_timestamp(boxInfo[31].toString());
			}
			boxInfoBeenList.add(boxInfoBeen);
		}

		return boxInfoBeenList;
	}
	
	public List<BoxInfoBeen> retBox2BoxInfoByManyConditionWipWHwithFAB(String box_id,
			String box_stat, String wo_id, String dest_shop, String bnk_flg,
			String nx_ope_id) {
		List<BoxInfoBeen> boxInfoBeenList = new ArrayList<BoxInfoBeen>();

		String queryCondition = "1=1";

		if (!Util.isSpaceCheck(box_id)) {
			queryCondition += " AND B.BOX_ID='" + box_id + "'";
		}
		if (!Util.isSpaceCheck(box_stat)) {
			queryCondition += " AND B.BOX_STAT='" + box_stat + "'";
		}
		if (!Util.isSpaceCheck(wo_id)) {
			queryCondition += " AND B.WO_ID_FK='" + wo_id + "'";
		}
		if (!Util.isSpaceCheck(dest_shop)) {
			queryCondition += " AND B.DEST_SHOP='" + dest_shop + "'";
		}
		if (!Util.isSpaceCheck(bnk_flg)) {
			queryCondition += " AND B.BNK_FLG='" + bnk_flg + "'";
		}
		if (!Util.isSpaceCheck(nx_ope_id)) {
			queryCondition += " AND B.NX_OPE_ID_FK='" + nx_ope_id + "'";
		}

		String queryString = " SELECT "
				+ " B.BOX_ID,B.BATCH_NO_FK, B.MDL_ID_FK, B.NX_OPE_ID_FK,B.NX_PATH_ID_FK, B.PRTY,B.PRD_QTY,B.PRD_GRADE_FK,B.LOT_ID_FK,B.BNK_FLG,"
				+ " W.MTRL_PROD_ID_FK,W.FM_MDL_ID_FK,W.CUT_MDL_ID_FK,W.FROM_THICKNESS,W.TO_THICKNESS, W.CUS_ID_FK, L.X_AXIS_CNT,L.Y_AXIS_CNT, B.WO_ID_FK, "
				+ " 	B.BOX_STAT,B.PRD_QTY,B.DEST_SHOP,B.EVT_USR,B.EVT_TIMESTAMP,B.SO_ID_FK,B.PRD_ADMIN_FLG,"
				+ " PG.OK_CNT,PG.NG_CNT,PG.GK_CNT,PG.LZ_CNT,PG.SC_CNT,B.WH_IN_TIMESTAMP"
				+ " FROM RET_BOX AS B"
				+ " INNER JOIN ( "
				+ " 	SELECT BOX_ID_FK, "
				+ " 		SUM(CASE WHEN PRD_GRADE='OK' THEN 1 ELSE 0 END ) OK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='NG' THEN 1 ELSE 0 END ) NG_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='GK' THEN 1 ELSE 0 END ) GK_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='LZ' THEN 1 ELSE 0 END ) LZ_CNT, "
				+ " 		SUM(CASE WHEN PRD_GRADE='SC' THEN 1 ELSE 0 END ) SC_CNT ";
				queryString += " FROM RET_PRD_INFO GROUP BY BOX_ID_FK ";
				queryString += " )PG ON B.BOX_ID = PG.BOX_ID_FK "
				+ " LEFT JOIN RET_WO W  ON B.WO_ID_FK = W.WO_ID"
				+ " LEFT JOIN BIS_MDL_DEF M ON W.MDL_ID_FK = M.MDL_ID"
				+ " LEFT JOIN BIS_LAYOT L ON M.LAYOT_ID_FK = L.LAYOT_ID"
				+ " WHERE  " + queryCondition + " GROUP BY B.BOX_ID ";

		List<Object[]> objList = retPrdInfoDao.findBySQL(queryString);
		for (int i = 0; i < objList.size(); i++) {
			BoxInfoBeen boxInfoBeen = new BoxInfoBeen();
			Object boxInfo[] = objList.get(i);

			boxInfoBeen.setBox_id(boxInfo[0].toString());
			if (boxInfo[2] != null) {
				boxInfoBeen.setMdl_id_fk(boxInfo[2].toString());
			}
			if (boxInfo[3] != null) {
				boxInfoBeen.setNx_ope_id_fk(boxInfo[3].toString());
			}
			if (boxInfo[4] != null) {
				boxInfoBeen.setNx_path_id_fk(boxInfo[4].toString());
			}
			if (boxInfo[5] != null) {
				boxInfoBeen.setPrty(boxInfo[5].toString());
			}
			if (boxInfo[6] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[6].toString()));
			}
			if (boxInfo[7] != null) {
				boxInfoBeen.setPrd_grade_fk(boxInfo[7].toString());
			}
			if (boxInfo[8] != null) {
				boxInfoBeen.setLot_id(boxInfo[8].toString());
			}
			if (boxInfo[9] != null) {
				boxInfoBeen.setBnk_flg(boxInfo[9].toString());
			}
			if (boxInfo[10] != null) {
				boxInfoBeen.setMtrl_prod_id(boxInfo[10].toString());
			}
			if (boxInfo[11] != null) {
				boxInfoBeen.setFm_mdl_id_fk(boxInfo[11].toString());
			}
			if (boxInfo[12] != null) {
				boxInfoBeen.setCut_mdl_id_fk(boxInfo[12].toString());
			}
			if (boxInfo[13] != null) {
				boxInfoBeen.setFrom_thickness(Double.valueOf(boxInfo[13]
						.toString()));
			}
			if (boxInfo[14] != null) {
				boxInfoBeen.setTo_thickness(Double.valueOf(boxInfo[14]
						.toString()));
			}
			if (boxInfo[15] != null) {
				boxInfoBeen.setCus_id(boxInfo[15].toString());
			}
			if (boxInfo[16] != null && boxInfo[17] != null) {
				boxInfoBeen.setMode_cnt(Integer.valueOf(boxInfo[16].toString())
						* Integer.valueOf(boxInfo[17].toString()));
			} else {
				boxInfoBeen.setMode_cnt(0);
			}
			if (boxInfo[18] != null) {
				boxInfoBeen.setWo_id(boxInfo[18].toString());
			}

			if (boxInfo[19] != null) {
				boxInfoBeen.setBox_stat(boxInfo[19].toString());
			}
			if (boxInfo[20] != null) {
				boxInfoBeen.setPrd_qty(Integer.valueOf(boxInfo[20].toString()));
			} else {
				boxInfoBeen.setPrd_qty(0);
			}
			if (boxInfo[21] != null) {
				boxInfoBeen.setDest_shop(boxInfo[21].toString());
				boxInfoBeen.setDest_shop_dsc(boxInfo[21].toString());
			}
			if (boxInfo[22] != null) {
				boxInfoBeen.setEvt_usr(boxInfo[22].toString());
			}
			if (boxInfo[23] != null) {
				boxInfoBeen.setEvt_timestamp(boxInfo[23].toString());
			}
			if (boxInfo[24] != null) {
				boxInfoBeen.setSo_id(boxInfo[24].toString());
			}
			if (boxInfo[25] != null){
				if (boxInfo[25].toString().substring(0, 1).equals(_YES)){
					boxInfoBeen.setPrd_admin_flg("Y");
				}else {
					boxInfoBeen.setPrd_admin_flg("N");
				}
			}
			if (boxInfo[26] != null) {
				boxInfoBeen.setOk_cnt(Integer.valueOf(boxInfo[26].toString()));
			} else {
				boxInfoBeen.setOk_cnt(0);
			}
			if (boxInfo[27] != null) {
				boxInfoBeen.setNg_cnt(Integer.valueOf(boxInfo[27].toString()));
			} else {
				boxInfoBeen.setNg_cnt(0);
			}
			if (boxInfo[28] != null) {
				boxInfoBeen.setGk_cnt(Integer.valueOf(boxInfo[28].toString()));
			} else {
				boxInfoBeen.setGk_cnt(0);
			}
			if (boxInfo[29] != null) {
				boxInfoBeen.setLz_cnt(Integer.valueOf(boxInfo[29].toString()));
			} else {
				boxInfoBeen.setLz_cnt(0);
			}
			if (boxInfo[30] != null) {
				boxInfoBeen.setSc_cnt(Integer.valueOf(boxInfo[30].toString()));
			} else {
				boxInfoBeen.setSc_cnt(0);
			}
			if (boxInfo[31] != null) {
				boxInfoBeen.setWh_in_timestamp(boxInfo[31].toString());
			}
			boxInfoBeenList.add(boxInfoBeen);
		}

		return boxInfoBeenList;
	}	

	public Ret_box setBoxYield(Ret_box box, List<Ret_prd_info> ret_prd_infoList) {
		int mode_cnt = 0, prd_qty = 0;
		int defCnt = 0, l_def_cnt = 0, b_def_cnt = 0;
		Double box_fst_yield = 0.0;// 制程良率 =1-制程不良/投入数（批次/箱）*100%
		Double box_snd_yield = 0.0;// 综合良率 =1-（制程不良+来料不良）/投入数*100%

		String[] lDefAry = { "L001", "L002", "L006", "L014", "L015", "L016", "L017" };
		String[] bDefAry = { "Z005", "Z006", "Z008", "Z010", "Z011", "Z014" };
		List<String> shtIdList = new ArrayList<String>();
		List<DefectInfoBeen2> defList = new ArrayList<DefectInfoBeen2>();

		if (null == ret_prd_infoList) {
			ret_prd_infoList = retPrdInfoDao.getShtListInfo(box.getBox_id());
			if (null == ret_prd_infoList) {
				ret_prd_infoList = new ArrayList<Ret_prd_info>();
			}
		}
		box.setBox_fst_yield(box_fst_yield);
		box.setBox_snd_yield(box_snd_yield);

		if (!ret_prd_infoList.isEmpty()) {
			prd_qty = ret_prd_infoList.size();
			if (null == ret_prd_infoList.get(0).getLayot_id_fk()
					|| null == ret_prd_infoList.get(0).getX_axis_cnt_fk()
					|| null == ret_prd_infoList.get(0).getY_axis_cnt_fk()) {
				logger.info("Get box [" + box.getBox_id()
						+ "] yield failed: prd's layout is null");
				return box;

			}
			mode_cnt = ret_prd_infoList.get(0).getX_axis_cnt_fk()
					* ret_prd_infoList.get(0).getY_axis_cnt_fk();

			for (Ret_prd_info prd : ret_prd_infoList) {
				shtIdList.add(prd.getPrd_seq_id());
			}
			defList = retPrdJgeDao.getDefInfoBeen2ByShtIdList(shtIdList);
			for (DefectInfoBeen2 def : defList) {
				defCnt += def.getDefect_cnt();
				if (Arrays.asList(lDefAry).contains(def.getDefect_code())) {
					l_def_cnt += def.getDefect_cnt();
				} else if (Arrays.asList(bDefAry)
						.contains(def.getDefect_code())) {
					b_def_cnt += def.getDefect_cnt();
				}
			}

			// Mode cnt use x-y value in ret_prd_info
			box_fst_yield = DoubleUtil.mul(DoubleUtil.sub(1, DoubleUtil.div(
					(double) (defCnt - l_def_cnt),
					(double) (mode_cnt * prd_qty), 4)), 100);
			box_snd_yield = DoubleUtil.mul(DoubleUtil.sub(1, DoubleUtil.div(
					(double) (defCnt), (double) (mode_cnt * prd_qty), 4)), 100);

			box.setBox_fst_yield(box_fst_yield);
			box.setBox_snd_yield(box_snd_yield);
		}

		return box;
	}

	public Ret_box setBoxYield(Ret_box box) {
		return setBoxYield(box, null);
	}

	public Boolean isInWipDestShop(Ret_box box) {
		if (box.getBnk_flg().equals(DEST_SHOP_BANK_FLG_WIP)
				&& box.getBox_stat().equals(PROC_SHIP)) {
			return true;
		}
		return false;
	}
	
	public int getPalletBoxCnt(String palletId) {
		String querySql = "SELECT COUNT(*) FROM Ret_box where pallet_id_fk ='"
				+ palletId + "'";
		return (int) this.getCountBySql(querySql);
	}
}
